using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;

namespace CMS_SUN_IF_Extraction
{
    class BAL
    {
        private DAL dao = new DAL();


        internal DataTable get_lst_VAT_center()
        {
            string sql = @"SELECT tVAT_Center.[VAT Center], tVAT_Center.VAT_Center_Desc, tVAT_Center.Select_ind
                            FROM tVAT_Center
                            WHERE tVAT_Center.Select_ind = 1
                            ORDER BY tVAT_Center.[VAT Center]";
            return dao.executeQuery(sql);
        }

        internal DataTable get_tbl_SysControl()
        {
            string sql = "Select * from tSysControl order by [ctrl_account_period] desc ";
            return dao.executeQuery(sql);

        }
        internal bool isNumeric(string p)
        {
            try
            {
                int t = int.Parse(p);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        internal int update_DB(DataTable tbl,string oldStartDate,string oldEndDate)
        {            
            DataRow r = tbl.Rows[0];
            string sql = "Update tSysControl "
                        + " Set Ctrl_Start_date='" + r["Ctrl_Start_Date"].ToString() + "',"
                        + " Ctrl_End_date='" + r["Ctrl_end_Date"].ToString() + "',"
                        + " Ctrl_Account_period=" + int.Parse(r["Ctrl_Account_period"].ToString());
            for (int i = 1; i <= 5; i++)
            {
                sql += ", Ctrl_VAT" + i + "_Center='" + r["Ctrl_VAT" + i + "_Center"].ToString() + "'";
                sql += ", Ctrl_VAT" + i + "_StartNum=" + int.Parse(r["Ctrl_VAT" + i + "_StartNum"].ToString());
                sql += ", Ctrl_VAT" + i + "_EndNum=" + int.Parse(r["Ctrl_VAT" + i + "_EndNum"].ToString());
            }
            sql += " where Ctrl_Start_date='" + oldStartDate + "'";
            sql += " and Ctrl_End_date='" + oldEndDate + "'";
            return dao.executeNonquery(sql);
        }

        internal int Delete_AB_Detail_SUN()
        {
            return dao.execute_Proc("sp_Delete_AB_Detail_SUN");
        }

       /* internal int Upd_Drug_T2Code()
        {
            return dao.execute_Proc("sp_Upd_Drug_T2Code");
        }*/
        internal int Upd_Drug_T2Code(string fromdate,string todate)
        {
            return dao.Upd_Drug_T2Code("sp_Upd_Drug_T2Code", fromdate, todate);
        }
        internal int SUN_SumSTDCode(string pVATCenter, string pStartDate, string pEndDate, int pAcct_period)
        {
            return dao.execute_proc("sp_SUN_SumSTDCode", pVATCenter, pStartDate, pEndDate, pAcct_period);
        }

        internal int SUN_SERV(string pVATCenter, string pStartDate, string pEndDate, int uAcct_period)
        {
            return dao.execute_proc("sp_qSUN_Serv",pVATCenter, pStartDate, pEndDate, uAcct_period);
        }

        internal int SUN_VAT(string pVATCenter, string pStartDate, string pEndDate, int uAcct_period)
        {
            return dao.execute_proc("sp_SUN_VAT",pVATCenter, pStartDate, pEndDate, uAcct_period);
        }

        internal int SUN_TOTAL(string pVATCenter, string pStartDate, string pEndDate, int uAcct_period)
        {
            return dao.execute_proc("sp_SUN_TOTAL", pVATCenter, pStartDate, pEndDate, uAcct_period);
        }

        public int Append_VAT_Check_Table(string pVAT_Center, int pVAT_Start_Num ,int pVAT_End_Num)
        {
            //int uTmp_VAT_NUm ;
            //uTmp_VAT_NUm = pVAT_Start_Num;
            //DataTable tVAT_Check_Tbl = dao.get_tVAT_Check_Table();
            //if (tVAT_Check_Tbl == null || tVAT_Check_Tbl.Rows.Count == 0)
            //    return;
            return dao.gAppend_VAT_Check_Table(pVAT_Center, pVAT_Start_Num, pVAT_End_Num);
        }

        internal DataTable get_lst_Extract_Checking()
        {
            string sql = "select * from qExtract_Checking_list"
                        + "  where qExtract_Checking_list.T0='24'"
                        + " order by qExtract_Checking_list.DATE_VISIT DESC";
            return dao.executeQuery(sql);
        }

        internal DataTable get_lst_Extract_Final_Output()
        {
            /*string sql = "select VAT_Number as 'VAT #',date_visit as 'Invoice Dt',Patient_Name,Accounts,T0,T2,T3,"
                        + " USD_Amount as Amount,DC_Ind as 'Dr/Cr',VN_Amount,Period,UPD_PAY_ACCTCODE as 'Payment Mode',"
                        + " UPD_PAYMENT_DATE as 'Payment Dt',UPD_CaseFile_Num as T4 "
                        + " from qExtract_Final_Output"
                        + " order by vat_number";*/
            string sql = "select VAT_Number as 'VAT #',date_visit as 'Invoice Dt',Patient_Name,T0,T2,T3,UPD_PAY_ACCTCODE as 'Payment Mode',T4_code,Accounts,"           
                        + " USD_Amount as Amount,DC_Ind as 'Dr/Cr',VN_Amount,UPD_CaseFile_Num,Period,"
                        + " UPD_PAYMENT_DATE as 'Payment Dt',UPD_VAT_NUM,UPD_VAT_Center,UPD_SUN_IMPORT "
                        + " from qExtract_Final_Output where UPD_PAYMENT_DATE is not null                               "
                        + " order by vat_number";
            return dao.executeQuery(sql);
        }

        internal void gVerify_b4_Export()
        {
            bool uPayment_Mode_OK, uAccount_Code_OK, uT2_Code_OK, uSum_Tally_OK, uVAT_has_noMissingCMS_OK;
            string uSQLStr;
            int uRecCount;
            string uErrorMsg = "";

            // Check for Blank Payment Mode (UPD_PAY_ACCTCODE) field
            uSQLStr = "Select count (*) AS uRecCount from qExtract_Final_Output where UPD_PAY_ACCTCODE = ' '";

            DataTable tbl = dao.executeQuery(uSQLStr);
            if (tbl == null)
                return;
            uRecCount = int.Parse(tbl.Rows[0][0].ToString());
            if (uRecCount > 0)
            {
                uPayment_Mode_OK = false;
                uErrorMsg ="There are " + uRecCount.ToString() + " records with BLANK Payment Mode. ";                
            }
            else
            {
                uPayment_Mode_OK = true;
            }

            // Check for Blank Account Code field
            uSQLStr = "Select count (*) AS uRecCount from qExtract_Final_Output where Accounts IS NULL";
            tbl = dao.executeQuery(uSQLStr);
            if (tbl == null)
                return;
            uRecCount = int.Parse(tbl.Rows[0][0].ToString());
            if (uRecCount > 0)
            {
                uAccount_Code_OK = false;
                uErrorMsg += "\n There are " + uRecCount.ToString() + " records with BLANK ACCOUNT CODE. ";
            }
            else
                uAccount_Code_OK = true;

            // Check for Blank T2 Code field
            uSQLStr = "Select count (*) AS uRecCount from qExtract_Final_Output where T2 = ' '";
            tbl = dao.executeQuery(uSQLStr);
            if (tbl == null)
                return;
            uRecCount = int.Parse(tbl.Rows[0][0].ToString());
            if (uRecCount > 0)
            {
                uT2_Code_OK = false;
                uErrorMsg += "\nThere are " + uRecCount.ToString() + " records with BLANK T2 CODE. ";
            }
            else
                uT2_Code_OK = true;

            // Check to ensure the XTOL line and SUM of other STD entries are tally
            uSQLStr = "Select count (*) AS uRecCount from qCheck_Amount_Tally";
            tbl = dao.executeQuery(uSQLStr);
            if (tbl == null)
                return;
            uRecCount = int.Parse(tbl.Rows[0][0].ToString());
            if (uRecCount > 0)
            {
                uSum_Tally_OK = false;
                uErrorMsg += "There are " + uRecCount + " records whose SUM of Proc, Phar, VAT & SRV do not tally with TOTAL amount. ";
            }
            else
                uSum_Tally_OK = true;

            //' Check to ensure there is no missing VAT invoice in the extraction
            uSQLStr = "Select count (*) AS uRecCount from qCheck_VAT_wnoCMS_Invoice";
            tbl = dao.executeQuery(uSQLStr);
            if (tbl == null)
                return;
            uRecCount = int.Parse(tbl.Rows[0][0].ToString());
            if (uRecCount > 0)
            {
                GlobalClass.gMissing_VAT = true;
                uVAT_has_noMissingCMS_OK = false;
                uErrorMsg += "\nThere are " + uRecCount.ToString() + " VAT # has no corresponding CMS Invoice. ";
            }
            else
            {
                GlobalClass.gMissing_VAT = false;
                uVAT_has_noMissingCMS_OK = true;
            }

            if (uPayment_Mode_OK && uAccount_Code_OK && uT2_Code_OK && uSum_Tally_OK && uVAT_has_noMissingCMS_OK)
            {
                GlobalClass.grecords_Verify_ok = true;
                MessageBox.Show("All records are valid !!");
            }
            else
            {
                MessageBox.Show(uErrorMsg,"Verifcation Check ...",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
                GlobalClass.grecords_Verify_ok = false;
            }
        }

        internal DataTable get_DataSource(string uSQLStr)
        {
            return dao.executeQuery(uSQLStr);
        }

        internal DataTable get_VAT_wNoCMS()
        {
            string sql = "Select * from qCheck_VAT_wNoCMS_Invoice ";
            return dao.executeQuery(sql);
        }

        internal int gCheck_XOS_Payment_Count()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        internal int Upd_ImportSUN_IND_newRevenue(string currDate)
        {
            return dao.Upd_ImportSUN_IND_newRevenue("sp_Upd_ImportSUN_IND_newRevenue",currDate);
        }

        internal int update_last_extract(string pCurrVATCenter,string pStartExtDate, string pEndExtDate,string pLastExtDate)
        {
            return dao.update_last_extract(pCurrVATCenter, pStartExtDate, pEndExtDate, pLastExtDate);
        }

        internal int update_tSysControl(string pCurVATCenter,string ctrl_StartDate,string ctrl_EndDate,  
                                        string pStart_ExtrDate, string pEnd_ExtrDate,
                                        string pStart_VATNum,string pEnd_VATNum)
        {
            string sql = "Update tSysControl"
                        + " set curr_start_extractDate='" + pStart_ExtrDate + "',"
                        + " curr_end_extractDate='" + pEnd_ExtrDate + "',"
                        + " curr_start_vat_num='" + pStart_VATNum + "',"
                        + " curr_end_vat_num='" + pEnd_VATNum + "',"
                        + " curr_vat_center='" + pCurVATCenter + "'"
                        //+ " where curr_vat_center='" + pCurVATCenter + "'"
                        + " where "
                        + " ctrl_start_date='" + ctrl_StartDate + "'"
                        + " and ctrl_end_date='" + ctrl_EndDate + "'";
            return dao.executeNonquery(sql);
            
        }

        internal string get_Last_Extract_Date()
        {
            string sql = "select * from tSysControl order by ctrl_start_date desc";
            DataTable tbl = dao.executeQuery(sql);
            if (tbl != null && tbl.Rows.Count > 0)
            {
                DataRow r = tbl.Rows[0];
                string last_extract_date = r["last_extract_date"] != null ? DateTime.Parse(r["last_extract_date"].ToString()).ToShortDateString() : "";
                return last_extract_date;
            }
            return "";
        }
    }
}
